const express = require('express');
const router = express.Router();
const sql = require("../db/index");
const Response = require("../utils/Response");

//获取全部学生信息
router.get('/students', async (req, res) => {
  //从数据库中获取学生信息
  const {id} = req.query;
  const [response] = await sql.query("SELECT * From s_students WHERE id = ?" ,[id])
  //返回学生信息
  res.send(new Response(200, "获取学生信息成功", response))
})

//获取输入指定搜索学生信息
router.get('/ss', async (req, res) => {
  //从数据库中获取学生信息
  const {studentName,studentId} = req.query;
  const [response] = await sql.query("SELECT * From s_students WHERE studentName = ? or studentId = ?" ,[studentName,studentId])
  //返回学生信息
  res.send(new Response(200, "获取学生信息成功", response))
}
)

// 分页查询学生信息
router.get('/pageQuery', async (req, res) => {
  //获取分页参数
  const { page, pageSize } = req.query;
  //查询学生总数量 用于计算总页数 [[{count: 10}]]
  const [totalCount] = await sql.query("SELECT COUNT(*) as count FROM s_students")//解构赋值
  const total = totalCount[0].count //直接拿到数值
  // LIMIT ?,?  表示从第(page-1)*pageSize条数据开始，获取pageSize条数据
  const [response] = await sql.query("SELECT * From s_students LIMIT ?,?", [(page - 1) * pageSize, parseInt(pageSize)])
  //返回学生信息
  res.send(new Response(200, "获取学生信息成功", { total, students: response }))
})

//删除学生信息
router.delete('/deleteById', async (req, res) => {
  //获取学生id,从请求参数中获取
  const { id } = req.query;
  if (!id) {
    res.send(new Response(400, "删除学生信息失败，id不能为空", null))
  } else {
    //删除学生信息
    await sql.query("DELETE FROM s_students WHERE id = ?", [id])
    //返回删除结果
    res.send(new Response(200, "删除学生信息成功", null))
  }
})

//添加学生信息
router.post('/add', async (req, res) => {
  //获取学生信息
  const { studentName, studentId,phoneNumber,email,accountStatus, password} = req.body;
  //判断id、学号、手机号、邮箱号是否重复
  const [ b ] = await sql.query("SELECT * FROM s_students WHERE studentId = ?", [studentId])
  const [ c ] = await sql.query("SELECT * FROM s_students WHERE phoneNumber = ?", [phoneNumber])
  const [ d ] = await sql.query("SELECT * FROM s_students WHERE email = ?", [email])
  if (b.length > 0) {
    res.send(new Response(411, "添加学生信息失败，学号重复，请重新输入", null))
  }else if (c.length > 0) {
    res.send(new Response(412, "添加学生信息失败，手机号重复，请重新输入", null))
  }else if (d.length > 0) {
    res.send(new Response(413, "添加学生信息失败，邮箱重复，请重新输入", null))
  }else {
  //添加学生信息
    await sql.query("INSERT INTO s_students (studentName, studentId,phoneNumber,email,accountStatus,password) VALUES (?, ?, ?, ?, ?,?)", [studentName, studentId,phoneNumber,email,accountStatus,password])
    //返回添加结果
    res.send(new Response(200, "添加学生信息成功",null))
  }
})

//管理员修改学生信息
router.put('/update', async (req, res) => {
  //获取学生信息
  const {id,studentName,studentId,phoneNumber,email,accountStatus,password} = req.body;
  //修改学生信息
  await sql.query("UPDATE s_students SET studentName=?, studentId=?, phoneNumber=?, email=?, accountStatus=? ,password = ?WHERE id = ?", [studentName, studentId,phoneNumber,email,accountStatus,password,id])
  //判断学号、手机号、邮箱号是否与其他用户重复
  const [ e ] = await sql.query("SELECT * FROM s_students WHERE studentId = ? AND id != ?", [studentId,id])
  const [ f ] = await sql.query("SELECT * FROM s_students WHERE phoneNumber = ?  AND id != ?", [phoneNumber,id])
  const [ g ] = await sql.query("SELECT * FROM s_students WHERE email = ? AND id != ?", [email,id])
  if (e.length > 0) {
    res.send(new Response(414, "修改学生信息失败，学号重复，请重新输入", null))
  }else if (f.length > 0) {
    res.send(new Response(415, "修改学生信息失败，手机号重复，请重新输入", null))
  }else if (g.length > 0) {
    res.send(new Response(416, "修改学生信息失败，邮箱重复，请重新输入", null))
  }else{
    res.send(new Response(200, "修改学生信息成功", null))
  }
})

//用户忘记密码修改密码
router.put('/updatePassword', async (req, res) => {
  //获取学生信息
  const {password,studentId,studentName,phoneNumber} = req.body;
  //修改学生信息
  await sql.query(
    "UPDATE s_students SET password=? WHERE studentId = ? AND studentName = ? and phoneNumber = ?", 
    [password,studentId,studentName,phoneNumber]
  )
  //返回修改结果
    res.send(new Response(200, "修改学生信息成功", password,studentName))
})
module.exports = router 